﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;
using System.Text;

namespace Common
{
    public class ToExcel
    {
        /// 导出Excel文件  
        /// </summary>  
        /// /// <param name="dataSet"></param>  
        /// <param name="dataTable">数据集</param>  
        /// <param name="isShowExcle">导出后是否打开文件</param>  
        /// <returns></returns>  
        //public static bool DataTableToExcel(string filePath, System.Data.DataTable dataTable, bool isShowExcle)
        //{
        //    if (string.IsNullOrEmpty(filePath))
        //        filePath = GetTimeStamp() + ".xls";
        //    //System.Data.DataTable dataTable = dataSet.Tables[0];  
        //    int rowNumber = dataTable.Rows.Count;
        //    int columnNumber = dataTable.Columns.Count;
        //    int colIndex = 0;
        //    if (rowNumber == 0)
        //    {
        //        return false;
        //    }
        //    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        //    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
        //    excel.Visible = isShowExcle;
        //    Microsoft.Office.Interop.Excel.Range range;
        //    foreach (DataColumn col in dataTable.Columns)
        //    {
        //        colIndex++;
        //        excel.Cells[1, colIndex] = col.ColumnName;
        //    }

        //    object[,] objData = new object[rowNumber, columnNumber];

        //    for (int r = 0; r < rowNumber; r++)
        //    {
        //        for (int c = 0; c < columnNumber; c++)
        //        {
        //            objData[r, c] = dataTable.Rows[r][c];
        //        }
        //    }
        //    range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[rowNumber + 1, columnNumber]];
        //    range.Value = objData;
        //    range.NumberFormatLocal = "@";
        //    worksheet.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //    return true;
        //}



        /// <summary>
        /// 获取时间戳
        /// </summary>
        /// <returns></returns>
        public static string GetTimeStamp()
        {
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds).ToString();
        }

        /// <summary>  
        /// 描述：把DataTable内容导出excel并返回客户端
        /// </summary>  
        /// <param name="dtData"></param>  
        /// <param name="header"></param>  
        /// <param name="fileName"></param>  
        /// <param name="mergeCellNums">要合并的列索引字典 格式：列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param>  
        /// <param name="mergeKey">作为合并项的标记列索引</param>  
        public static void DataTable2Excel(DataTable dt, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int? mergeKey)
        {
            System.Web.UI.WebControls.GridView gvExport = null;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;
            if (dt != null)
            {
                //导出Excel文件
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);

                //重新定义一个gridview
                gvExport = new GridView();
                gvExport.DataSource = dt.DefaultView;
                gvExport.AllowPaging = false;
                //优化数据导出显示，如身份证，12-1等显示异常问题
                gvExport.RowDataBound += new GridViewRowEventHandler(dgExport_RowDataBound);
                gvExport.DataBind();
                //处理表头
                if (header != null && header.Length > 0)
                {
                    gvExport.HeaderRow.Cells.Clear();
                    gvExport.HeaderRow.Cells.AddRange(header);
                }
                //合并单元格
                if (mergeCellNums != null && mergeCellNums.Count > 0)
                {
                    foreach (int cellNum in mergeCellNums.Keys)
                    {
                        MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey);
                    }
                }
                //返回客户端
                gvExport.RenderControl(htmlWriter);
                StreamWriter sw = new StreamWriter(fileName, false, Encoding.GetEncoding("gb2312"));
                sw.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
                sw.Flush();
                sw.Close();
                sw.Dispose();
                strWriter.Close();
                strWriter.Dispose();
                //SaveAsExcel2003(fileName);
            }
        }

        /// <summary>  
        /// 描述：行绑定事件  
        /// </summary>  
        /// <param name="sender"></param>  
        /// <param name="e"></param>  
        protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                foreach (TableCell cell in e.Row.Cells)
                {
                    //优化导出数据显示，如身份证、12-1等显示异常问题  
                    if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$"))
                    {
                        cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                    }
                }
            }
        }

        /// <summary>     
        /// 描述：合并GridView列中相同的行
        /// </summary>     
        /// <param   name="gvExport">GridView对象</param>     
        /// <param   name="cellNum">需要合并的列</param>     
        /// <param name="mergeMode">合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项</param>  
        /// <param name="mergeKey">作为合并项的标记列索引</param>  
        public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey)
        {
            int i = 0, rowSpanNum = 1;
            System.Drawing.Color alterColor = System.Drawing.Color.LightGray;
            while (i < gvExport.Rows.Count - 1)
            {
                GridViewRow gvr = gvExport.Rows[i];
                for (++i; i < gvExport.Rows.Count; i++)
                {
                    GridViewRow gvrNext = gvExport.Rows[i];
                    if ((!mergeKey.HasValue || (mergeKey.HasValue && (gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text) || " ".Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode == 1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) || (mergeMode == 2 && " ".Equals(gvrNext.Cells[cellNum].Text.Trim())) || (mergeMode == 3 && (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text || " ".Equals(gvrNext.Cells[cellNum].Text.Trim())))))
                    {
                        gvrNext.Cells[cellNum].Visible = false;
                        rowSpanNum++;
                        //gvrNext.BackColor = gvr.BackColor;  
                        if (alterColor == System.Drawing.Color.White)
                            gvrNext.BackColor = System.Drawing.Color.LightGray;
                    }
                    else
                    {
                        gvr.Cells[cellNum].RowSpan = rowSpanNum;
                        rowSpanNum = 1;
                        //间隔行加底色，便于阅读  
                        if (mergeKey.HasValue && cellNum == mergeKey.Value)
                        {
                            if (alterColor == System.Drawing.Color.White)
                            {
                                gvr.BackColor = System.Drawing.Color.LightGray;
                                alterColor = System.Drawing.Color.LightGray;
                            }
                            else
                            {
                                alterColor = System.Drawing.Color.White;
                            }
                        }
                        break;
                    }
                    if (i == gvExport.Rows.Count - 1)
                    {
                        gvr.Cells[cellNum].RowSpan = rowSpanNum;
                        if (mergeKey.HasValue && cellNum == mergeKey.Value)
                        {
                            if (alterColor == System.Drawing.Color.White)
                                gvr.BackColor = System.Drawing.Color.LightGray;
                        }
                    }
                }
            }
        }

        /// <summary>  
        /// 另存为2003格式Excel  
        /// </summary>  
        /// <param name="filePath"></param>  
        //public static void SaveAsExcel2003(string filePath)
        //{
        //    try
        //    {
        //        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        //        app.DisplayAlerts = false; //取消所有提示窗口
        //        Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
        //        Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //        //另存为Excel2003格式  
        //        wb.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
        //        wb.Close(Type.Missing, Type.Missing, Type.Missing);
        //        wbs.Close();
        //        app.Quit();
        //        wb = null;
        //        wbs = null;
        //        app = null;
        //        GC.Collect();
        //    }
        //    catch(Exception ex)
        //    {
        //        throw ex;
                
        //    }
        //}
    }
}
